﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
       
        /// <summary>
        /// 增加一條程序版本類別信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddVersionInfo(SoftwareVerInfo Verinfo)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@Name",(DbType)SqlDbType.NVarChar,200,Verinfo.FileName),
                                       DbHelper.MakeInParam("@Xuhao",(DbType)SqlDbType.Int,int.MinValue,Verinfo.Xuhao),
                                       DbHelper.MakeInParam("@Show",(DbType)SqlDbType.Bit,4,Verinfo.Show)
                                   };
            string commandText = "INSERT INTO [dbo].[XS_SoftwareVer]([Name],[Xuhao],[Show]) VALUES(@Name,@Xuhao,@Show)";

            return Utils.StrToInt(DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar), -1);
        }

        /// <summary>
        ///  增加一條程序下載信息(語言不為空時)
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddProcedInfo(SoftwareDownInfo Procfo)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@VerType",(DbType)SqlDbType.Int,int.MinValue,Procfo.VerType),
                                       DbHelper.MakeInParam("@Language",(DbType)SqlDbType.NVarChar,100,Procfo.Language),
                                       DbHelper.MakeInParam("@Version",(DbType)SqlDbType.NVarChar,200,Procfo.Version),
                                       DbHelper.MakeInParam("@MD5",(DbType)SqlDbType.NVarChar,200,Procfo.Md5),
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,Procfo.Content),
                                       DbHelper.MakeInParam("@DownUrl1",(DbType)SqlDbType.NVarChar,300,Procfo.DownUrl1),
                                       DbHelper.MakeInParam("@DownUrl2",(DbType)SqlDbType.NVarChar,300,Procfo.DownUrl2),
                                       DbHelper.MakeInParam("@IsDown",(DbType)SqlDbType.Bit,4,Procfo.IsDown),
                                       DbHelper.MakeInParam("@Admin",(DbType)SqlDbType.NVarChar,50,Procfo.Admin),
                                       DbHelper.MakeInParam("@Time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,Procfo.Time)
                                   
                                   };
            string commandText = "INSERT INTO [dbo].[XS_SoftwarePro]([VerType],[Language],[Version],[MD5],[Content],[DownUrl1],[DownUrl2],[Hits],[IsDown],[Admin],[Time])";
            commandText += "VALUES(@VerType,@Language,@Version,@MD5,@Content,@DownUrl1,@DownUrl2,0,@IsDown,@Admin,@Time)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        ///  增加一條程序下載信息(語言為空時)
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddProcedInfo1(SoftwareDownInfo Procfo)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@VerType",(DbType)SqlDbType.Int,int.MinValue,Procfo.VerType),
                                       DbHelper.MakeInParam("@Version",(DbType)SqlDbType.NVarChar,200,Procfo.Version),
                                       DbHelper.MakeInParam("@MD5",(DbType)SqlDbType.NVarChar,200,Procfo.Md5),
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,Procfo.Content),
                                       DbHelper.MakeInParam("@DownUrl1",(DbType)SqlDbType.NVarChar,300,Procfo.DownUrl1),
                                       DbHelper.MakeInParam("@DownUrl2",(DbType)SqlDbType.NVarChar,300,Procfo.DownUrl2),
                                       DbHelper.MakeInParam("@IsDown",(DbType)SqlDbType.Bit,4,Procfo.IsDown),
                                       DbHelper.MakeInParam("@Admin",(DbType)SqlDbType.NVarChar,50,Procfo.Admin),
                                       DbHelper.MakeInParam("@Time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,Procfo.Time)
                                    
                                   };
            string commandText = "INSERT INTO [dbo].[XS_SoftwarePro]([VerType],[Language],[Version],[MD5],[Content],[DownUrl1],[DownUrl2],[Hits],[IsDown],[Admin],[Time])";
            commandText += "VALUES(@VerType,'繁體中文',@Version,@MD5,@Content,@DownUrl1,@DownUrl2,0,@IsDown,@Admin,@Time)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 獲取信息顯示程序版本類別列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetVersionInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_SoftwareVer] order by [Xuhao] asc";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        /// 獲取信息綁定程序版本類別
        /// </summary>
        /// <returns></returns>
        public DataSet BindVersionInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_SoftwareVer] WHERE [Show]=1 order by [Xuhao] asc";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        /// 獲取信息顯示程序下載列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetProcedInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_SoftwarePro] order by [Time] desc";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得下載類表前35條記錄
        /// </summary>
        /// <returns></returns>
        public DataSet GetSoftWareProcAndVerTop35Rows()
        {
            string commandText = "SELECT TOP 35 PERCENT dbo.XS_SoftwarePro.*, dbo.XS_SoftwareVer.Name, dbo.XS_SoftwareVer.Show";
            commandText += " FROM  dbo.XS_SoftwarePro INNER JOIN dbo.XS_SoftwareVer ON dbo.XS_SoftwarePro.VerType = dbo.XS_SoftwareVer.ID";
            commandText += " WHERE (dbo.XS_SoftwareVer.Show = 1)ORDER BY dbo.XS_SoftwarePro.Time DESC";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText);
        }
        /// <summary>
        /// 根據下載主表ID取得下載類表
        /// </summary>
        /// <returns></returns>
        public DataSet GetSoftWareProcAndVerById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "SELECT dbo.XS_SoftwarePro.*, dbo.XS_SoftwareVer.Name, dbo.XS_SoftwareVer.Show";
            commandText += " FROM  dbo.XS_SoftwarePro INNER JOIN dbo.XS_SoftwareVer ON dbo.XS_SoftwarePro.VerType = dbo.XS_SoftwareVer.ID";
            commandText += " WHERE (dbo.XS_SoftwarePro.id =@Id)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText,getPar);
        }

        /// <summary>
        ///  根據類別id顯示相應的類別名稱
        /// </summary>
        /// <returns></returns>
        public SoftwareVerInfo GetVersionName(string Id)
        {
            
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, Id);
            string commandText = "SELECT Name FROM [dbo].[XS_SoftwareVer] WHERE Id=@Id";
            SoftwareVerInfo vinf = new SoftwareVerInfo();
            DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
            if (ds.Tables[0].Rows.Count > 0)
            {
                vinf.FileName = ds.Tables[0].Rows[0]["Name"].ToString();        
            }
            return vinf;
  
        }

        /// <summary>
        ///  獲取一條要修改的程序下載信息顯示
        /// </summary>
        /// <returns></returns>
        public SoftwareDownInfo GetProcedId(string ProcId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, ProcId);
            string commandText = "SELECT * FROM [dbo].[XS_SoftwarePro] WHERE Id=@Id";
            SoftwareDownInfo prinf = new SoftwareDownInfo();
            DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
            if (ds.Tables[0].Rows.Count > 0)
            {
                prinf.Version = ds.Tables[0].Rows[0]["Version"].ToString();
                prinf.Language = ds.Tables[0].Rows[0]["Language"].ToString();
                prinf.Md5 = ds.Tables[0].Rows[0]["Md5"].ToString();
                prinf.DownUrl1 = ds.Tables[0].Rows[0]["DownUrl1"].ToString();
                prinf.DownUrl2 = ds.Tables[0].Rows[0]["DownUrl2"].ToString();
                prinf.Time = ds.Tables[0].Rows[0]["Time"].ToString();
                prinf.Content = ds.Tables[0].Rows[0]["Content"].ToString();
                prinf.VerType = Convert.ToInt16(ds.Tables[0].Rows[0]["VerType"].ToString());
                if (bool.Parse(ds.Tables[0].Rows[0]["IsDown"].ToString()) == true)
                {
                    prinf.IsDown = true;
                }
                if (bool.Parse(ds.Tables[0].Rows[0]["IsDown"].ToString()) == false)
                {
                    prinf.IsDown = false;
                }
            }
            return prinf;

        }

        /// <summary>
        /// 更新一條程序版本類別信息
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public int UpVersionInfo(string Name, string Xuhao, bool Show, string Id)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@Name",(DbType)SqlDbType.NVarChar,200,Name),
                                       DbHelper.MakeInParam("@Xuhao",(DbType)SqlDbType.Int,int.MinValue,Xuhao),
                                       DbHelper.MakeInParam("@Show",(DbType)SqlDbType.Bit,4,Show),
                                       DbHelper.MakeInParam("@Id",(DbType)SqlDbType.Int,int.MinValue,Id)
                                   };
            string commandText = "UPDATE [dbo].[XS_SoftwareVer] SET [Name]=@Name,[Xuhao]=@Xuhao,[Show]=@Show where [Id]=@Id";
         
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

        }


        /// <summary>
        /// 更新一條要修改的程序下载信息(语言为空)
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public int UpProcedInfo(int VerType, string Version, string Md5, string Conent, string DownUrl1, string DownUrl2, string Time, bool IsDown,string Id)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@VerType",(DbType)SqlDbType.Int,int.MinValue,VerType),
                                       DbHelper.MakeInParam("@Version",(DbType)SqlDbType.NVarChar,200,Version),
                                       DbHelper.MakeInParam("@Md5",(DbType)SqlDbType.NVarChar,200,Md5),
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,Conent),
                                       DbHelper.MakeInParam("@DownUrl1",(DbType)SqlDbType.NVarChar,300,DownUrl1),
                                       DbHelper.MakeInParam("@DownUrl2",(DbType)SqlDbType.NVarChar,300,DownUrl2),
                                       DbHelper.MakeInParam("@Time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,Time),
                                       DbHelper.MakeInParam("@IsDown",(DbType)SqlDbType.Bit,4,IsDown),
                                       DbHelper.MakeInParam("@Id",(DbType)SqlDbType.Int,int.MinValue,Id)
                                   };
            string commandText = "UPDATE [dbo].[XS_SoftwarePro] SET [VerType]=@VerType,[Language]='繁體中文',[Version]=@Version,[Md5]=@Md5,[Content]=@Content,[DownUrl1]=@DownUrl1,[DownUrl2]=@DownUrl2,[Time]=@Time,[IsDown]=@IsDown where [Id]=@Id";

            return Utils.StrToInt(DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar), -1);
        }

        /// <summary>
        /// 更新一條要修改的程序下载信息(语言不为空)
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public int UpProcedInfo1(int VerType, string Language, string Version, string Md5, string Conent, string DownUrl1, string DownUrl2, string Time, bool IsDown, string Id)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@VerType",(DbType)SqlDbType.Int,int.MinValue,VerType),
                                       DbHelper.MakeInParam("@Language",(DbType)SqlDbType.NVarChar,200,Language),
                                       DbHelper.MakeInParam("@Version",(DbType)SqlDbType.NVarChar,200,Version),
                                       DbHelper.MakeInParam("@Md5",(DbType)SqlDbType.NVarChar,200,Md5),
                                       DbHelper.MakeInParam("@Content",(DbType)SqlDbType.NText,1073741823,Conent),
                                       DbHelper.MakeInParam("@DownUrl1",(DbType)SqlDbType.NVarChar,300,DownUrl1),
                                       DbHelper.MakeInParam("@DownUrl2",(DbType)SqlDbType.NVarChar,300,DownUrl2),
                                       DbHelper.MakeInParam("@Time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,Time),
                                       DbHelper.MakeInParam("@IsDown",(DbType)SqlDbType.Bit,4,IsDown),
                                       DbHelper.MakeInParam("@Id",(DbType)SqlDbType.Int,int.MinValue,Id)
                                   };
            string commandText = "UPDATE [dbo].[XS_SoftwarePro] SET [VerType]=@VerType,[Language]=@Language,[Version]=@Version,[Md5]=@Md5,[Content]=@Content,[DownUrl1]=@DownUrl1,[DownUrl2]=@DownUrl2,[Time]=@Time,[IsDown]=@IsDown where [Id]=@Id";

            return Utils.StrToInt(DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar), -1);
        }

        /// <summary>
        /// 更新設置是否提供程序下載
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public int UpIsProc(bool IsDown, string Id)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@IsDown",(DbType)SqlDbType.Bit,4,IsDown),
                                       DbHelper.MakeInParam("@Id",(DbType)SqlDbType.Int,int.MinValue,Id)
                                   };
            string commandText = "UPDATE [dbo].[XS_SoftwarePro] SET [IsDown]=@IsDown where [Id]=@Id";

            return Utils.StrToInt(DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar), -1);
        }

          /// <summary>
        /// 根據程序版本類別ID刪除版本信息
        /// </summary>
        /// <returns></returns>
        public int DelVersionInfo(string Id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, Id);
            string commandText = "DELETE FROM [dbo].[XS_SoftwareVer] WHERE [Id]=@Id";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據程序版本類別ID刪除程序信息
        /// </summary>
        /// <returns></returns>
        public int DelProcedInfo(string Id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@VerType", (DbType)SqlDbType.Int, int.MaxValue, Id);
            string commandText = "DELETE FROM [dbo].[XS_SoftwarePro] WHERE [VerType]=@VerType";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        ///根據下載id刪除程序下載信息
        /// </summary>
        /// <returns></returns>
        public int DelProcedId(string Id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, int.MaxValue, Id);

            string commandText = "DELETE FROM [dbo].[XS_SoftwarePro] WHERE [Id]=@Id";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        

    }
}
